# -*- encoding: utf-8 -*-
#import odoo.addons.decimal_precision as dp
from odoo import models, fields, api, _
from odoo.addons import base_cw
from odoo.exceptions import UserError
from odoo.tools.float_utils import float_round, float_compare


class department_expense_summary(models.Model):
    _name = 'department.expense.summary'
    _description = u'部门费用明细表'
    _rec_name = 'account_id'

    period_id = fields.Many2one('account.period', u'期别', ondelete="set null")
    department_id = fields.Many2one('res.partner', u'部门', ondelete="set null")
    account_id = fields.Many2one('cncw.account', u'会科', ondelete="set null")
    amount = fields.Float(u'金额', digits='Amount')
    year_amount = fields.Float(u'年累金额', digits='Amount')
    rate = fields.Float(u'比重%', digits=(16, 2))
    year_rate = fields.Float(u'年累比重%', digits=(16, 4))

    def init(self):
        self._cr.execute("""
                    CREATE OR REPLACE FUNCTION department_expense_summary_settle(
                            period_t integer,
                            uid_t integer)
                      RETURNS void AS
                    /*
                    部门费用明细表
                    drop FUNCTION department_expense_summary_settle(integer,integer)
                    select department_expense_summary_settle(10,1)
                    select * from department_expense_summary
                    select *  from expense_summary_t
                    select *  from account_period
                    delete from department_expense_summary
                    */
                    $BODY$

                    declare
                            start_date_t date;
                            end_date_t date;
                            total_amount numeric;
                            total_year_amount numeric;
                            total_rate numeric;
                            total_year_rate numeric;
                            rec record;

                    begin
                            create temp table expense_summary_t(
                              sub_account_id integer,-- 部门
                                      account_id integer, -- 会科
                              year_amount numeric, -- 年累金额
                              amount numeric -- 金额
                            );

                            create temp table expense_summary_ok(
                              account_id integer, -- 会科
                              sub_account_id integer, -- 部门
                              amount numeric, -- 金额
                              year_amount numeric, -- 年累金额
                              rate numeric, -- 比重%
                              year_rate numeric -- 年累比重%
                            );


                            select date_start,date_stop into start_date_t,end_date_t from account_period where id=period_t;
                            raise notice '%,%',start_date_t,end_date_t;
                            insert into expense_summary_t(account_id,sub_account_id,amount)
                        select a.account_id,d.sub_account_id,sum(coalesce(a.debit,0)-coalesce(a.credit,0)) as amount
                          from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                          left join cncw_account c on a.account_id=c.id
                                          left join sub_account_line d on a.id=d.voucher_line_id
                         where b.state='audited'
                           and b.date between start_date_t and end_date_t
                           and c.account_category in ('6') --费用类
                         group by a.account_id,d.sub_account_id;

                            insert into expense_summary_t(account_id,sub_account_id,year_amount)
                        select a.account_id,d.sub_account_id,sum(coalesce(a.debit,0)-coalesce(a.credit,0)) as amount
                          from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                          left join cncw_account c on a.account_id=c.id
                                          left join sub_account_line d on a.id=d.voucher_line_id
                         where b.state='audited'
                           and b.date between (substring(start_date_t::character varying,1,4)||'-01-01')::date and (substring(start_date_t::character varying,1,4)||'-12-31')::date
                           and c.account_category in ('6') --费用类
                         group by a.account_id,d.sub_account_id;

                         insert into expense_summary_ok(account_id,sub_account_id,amount,year_amount)
                         select account_id,sub_account_id,
                                sum(coalesce(amount,0)) as amount,
                                sum(coalesce(year_amount,0)) as year_amount
                           from expense_summary_t
                          group by account_id,sub_account_id;
                             --计算各部门 费用比重%
                         for rec in select distinct sub_account_id  from  expense_summary_ok where   sub_account_id is not null
                         loop
                                 total_amount:=0.0;
                                 total_year_amount:=0.0;

                             select  sum(coalesce(amount,0)),sum(coalesce(year_amount,0)) into total_amount,total_year_amount
                               from expense_summary_ok
                              where expense_summary_ok.sub_account_id =rec.sub_account_id;

                             update expense_summary_ok set rate=round(abs(amount)/abs(total_amount),4)*100
                              where coalesce(total_amount,0)<>0
                                and expense_summary_ok.sub_account_id =rec.sub_account_id;

                              update expense_summary_ok set year_rate=round(abs(year_amount)/abs(total_year_amount),4)*100
                              where coalesce(total_year_amount,0)<>0
                                        and expense_summary_ok.sub_account_id =rec.sub_account_id;

                                 total_rate:=0.0;
                                 total_year_rate:=0.0;
                             select  sum(coalesce(rate,0)),sum(coalesce(year_rate,0)) into total_rate,total_year_rate
                              from expense_summary_ok
                             where expense_summary_ok.sub_account_id =rec.sub_account_id;

                             if total_rate<>100.0 then
                                update expense_summary_ok a set rate=a.rate+100-total_rate
                                  from (select *  from expense_summary_ok where rate=(select max(coalesce(rate,0)) from expense_summary_ok ) limit 1) b
                                 where a.account_id=b.account_id and a.sub_account_id=b.sub_account_id
                                   and a.sub_account_id =rec.sub_account_id;
                             end if;

                             if total_year_rate<>100.0 then
                                update expense_summary_ok a set year_rate=a.year_rate+100-total_year_rate
                                  from (select * from expense_summary_ok where year_rate=(select max(coalesce(year_rate,0)) from expense_summary_ok ) limit 1) b
                                 where a.account_id=b.account_id and a.sub_account_id=b.sub_account_id
                                   and a.sub_account_id =rec.sub_account_id;
                             end if;
                            end loop;
                            delete from department_expense_summary where period_id=period_t;

                         insert into department_expense_summary(period_id,account_id,department_id,amount,year_amount,rate,year_rate,create_uid,create_date)
                         select period_t,account_id,sub_account_id,amount,year_amount,rate,year_rate,uid_t,(now() at time zone 'UTC')
                           from expense_summary_ok;

                        drop table expense_summary_ok;
                        drop table expense_summary_t;
                     end;
                     $BODY$
                      LANGUAGE plpgsql VOLATILE
                      COST 100;
                   """)